﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Text;
using System.Windows.Forms;

namespace WinFormRapor
{
    class RaporClass
    {
        public string FilePath = "";

        public void RaporOlustur(string raporSql, string raporAdi)
        {
            StringBuilder sb = new StringBuilder();
            using (SqlConnection sqlConn = new SqlConnection("server=" + Config.Get.Server + ";user=" + Config.Get.User + ";pwd=" + Config.Get.Pass + ";database=" + Config.Get.DbName + ";"))
            {
                sqlConn.Open();
                using (SqlDataAdapter sqlAdapt = new SqlDataAdapter(raporSql, sqlConn))
                {
                    sqlAdapt.SelectCommand.CommandTimeout = 9999;
                    using (SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt))
                    {
                        DataTable dt = new DataTable();
                        try
                        {
                            if (raporSql.IndexOf("exec") > -1)
                            {
                                sqlAdapt.SelectCommand.CommandType = CommandType.StoredProcedure;
                            }
                            sqlAdapt.Fill(dt);
                            sqlAdapt.Dispose();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Hata..!!  \n" + ex.Message);
                        }
                        sb.AppendLine("<html>");
                        sb.AppendLine("<head>");
                        sb.AppendLine("<title>");
                        sb.AppendLine("Rapor Listesi");
                        sb.AppendLine("</title>");
                        sb.AppendLine("<meta charset=iso-8859-9 />");
                        sb.AppendLine("<meta name='author' content='yasin ozbey' />");
                        sb.AppendLine("<meta http-equiv='Page-Exit' content='revealTrans(Duration=5.0,Transition=6)'>");
                        sb.AppendLine("<meta name='copyright' content='copyright 2012 www.mikroyadim.com' />");
                        sb.AppendLine("<meta name='viewport' content='width=device-width; initial-scale=1.0; maximum-scale=1.0;' />");
                        sb.AppendLine("<meta http-equiv='Page-Enter' content='progid:DXImageTransform.Microsoft.Slide(duration=2,irisStyle=star)'> ");
                        sb.AppendLine("<meta http-equiv='Page-Exit' content='progid:DXImageTransform.Microsoft.Slide(duration=2,irisStyle=star)'>");
                        sb.AppendLine("<style>");
                        sb.AppendLine("@import url('../iphone.css');");
                        sb.AppendLine("</style>");
                        sb.AppendLine("<script type='text/javascript' src='../orientation.js'></script>");
                        sb.AppendLine("<script type='text/javascript'>");
                        sb.AppendLine("window.addEventListener('load', function() { setTimeout(loaded, 100) }, false);");
                        sb.AppendLine("function loaded() {");
                        sb.AppendLine("document.getElementById('page_wrapper').style.visibility = 'visible';");
                        sb.AppendLine("window.scrollTo(0, 1); }");
                        sb.AppendLine("</script>");
                        sb.AppendLine("</head>");
                        sb.AppendLine("<body onorientationchange='updateOrientation()'>");
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            sb.AppendLine("<table style='width: auto; height: auto' id='hor-minimalist-b'>");
                            sb.AppendLine("<tr> <td colspan='2' style='width: auto'> <h2>" + raporAdi + "</td> <td colspan='3' style='width: auto'></td> <td colspan='2'>" + DateTime.Now + "</br></h3></td> </tr>");
                            sb.AppendLine("</table>");
                            sb.AppendLine("<table id='hor-minimalist-b' summary='Employee Pay Sheet'>");
                            sb.AppendLine(" <thead> <tr align='left'> ");
                            for (int k = 0; k < dt.Columns.Count; k++)
                            {
                                sb.AppendLine("<th scope='col' align='left'>" + dt.Columns[k].ColumnName + "</th> ");
                            }
                            sb.AppendLine(" </thead> </tr>");

                            for (int j = 0; j < dt.Rows.Count; j++)
                            {
                                sb.AppendLine("<tbody> <tr>");
                                for (int l = 0; l < dt.Columns.Count; l++)
                                {
                                    if (dt.Columns[l].DataType == typeof(double) || dt.Columns[l].DataType == typeof(decimal))
                                    {
                                        if (dt.Rows[j][l] != DBNull.Value)
                                            sb.AppendLine("<td>" + Convert.ToDecimal(dt.Rows[j][l]).ToString("n2") + "</td>");
                                        else
                                            sb.AppendLine("<td>" + Convert.ToDecimal(0).ToString("n2") + "</td>");
                                    }
                                    else
                                    {
                                        sb.AppendLine("<td>" + dt.Rows[j][l] + "</td>");
                                    }
                                }
                                sb.AppendLine("</tbody> </tr>");
                            }
                            sb.AppendLine("</table>");
                        }
                        sb.AppendLine("</body>");
                        sb.AppendLine("</html>");
                        FileStream fs;
                        if (!Directory.Exists(Path.GetTempPath() + "\\" + Config.Get.FirmaKod))
                            Directory.CreateDirectory(Path.GetTempPath() + "\\" + Config.Get.FirmaKod);

                        FilePath = Path.GetTempPath() + Config.Get.FirmaKod + "\\" + raporAdi + "-" + Config.Get.FirmaKod + ".html";
                        if (File.Exists(FilePath))
                            fs = new FileStream(FilePath, FileMode.Truncate, FileAccess.ReadWrite);
                        else
                            fs = new FileStream(FilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                        //Encoding m_Encoding;
                        //m_Encoding = Encoding.GetEncoding("iso-8859-9");
                        StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                        sw.Write(sb.ToString());
                        sw.Close();
                        fs.Close();
                        FTPuploadFile("ftp.mikroyardim.com", "mikroyardim.com-dev", "1Q2w3e4r", FilePath);
                    }
                }
                sqlConn.Close();
            }
        }

        public void GrafikOlustur(string raporSql, string raporAdi, byte grafikTipi)
        {
            StringBuilder sb = new StringBuilder();
            using (SqlConnection sqlConn = new SqlConnection("server=" + Config.Get.Server + ";user=" + Config.Get.User + ";pwd=" + Config.Get.Pass + ";database=" + Config.Get.DbName + ";"))
            {
                sqlConn.Open();
                using (SqlDataAdapter sqlAdapt = new SqlDataAdapter(raporSql, sqlConn))
                {
                    sqlAdapt.SelectCommand.CommandTimeout = 9999;
                    using (SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt))
                    {
                        DataTable dt = new DataTable();
                        try
                        {
                            sqlAdapt.Fill(dt);
                            sqlAdapt.Dispose();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Hata..!!  \n" + ex.Message);
                        }

                        sb.AppendLine("<html>");
                        sb.AppendLine("  <head>");
                        sb.AppendLine("<title>");
                        sb.AppendLine(raporAdi);
                        sb.AppendLine("</title>");
                        sb.AppendLine("<meta charset=iso-8859-9 />");
                        sb.AppendLine("<meta name='author' content='yasin ozbey' />");
                        sb.AppendLine("<meta http-equiv='Page-Exit' content='revealTrans(Duration=5.0,Transition=6)'>");
                        sb.AppendLine("<meta name='copyright' content='copyright 2012 www.mikroyadim.com' />");
                        sb.AppendLine("<meta name='viewport' content='width=device-width; initial-scale=1.0; maximum-scale=1.0;' />");
                        sb.AppendLine("    <script type=\"text/javascript\" src=\"https://www.google.com/jsapi\"></script>");
                        sb.AppendLine("    <script type=\"text/javascript\">");
                        sb.AppendLine("      google.load(\"visualization\", \"1\", {packages:[\"corechart\"]});");
                        sb.AppendLine("      google.setOnLoadCallback(drawChart);");
                        sb.AppendLine("      function drawChart() {");
                        sb.AppendLine("        var data = google.visualization.arrayToDataTable([");


                        string col1name = "";

                        if (dt != null && dt.Rows.Count > 0)
                        {
                            col1name = dt.Columns[0].ColumnName;
                            string baslik = "          [";
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                baslik += string.Format("'{0}', ", dt.Columns[i].ColumnName);
                            }
                            baslik = baslik.Remove(baslik.Length - 2);
                            sb.AppendLine(baslik + "],");

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                baslik = "          [";
                                for (int c = 0; c < dt.Columns.Count; c++)
                                {
                                    if (dt.Columns[c].DataType == typeof(string))
                                    {
                                        baslik += string.Format("'{0}', ", dt.Rows[i][c].ToString());
                                    }
                                    else
                                    {
                                        baslik += string.Format("{0}, ", dt.Rows[i][c].ToString());
                                    }
                                }
                                baslik = baslik.Remove(baslik.Length - 2);
                                sb.AppendLine(baslik + "],");
                            }
                            sb.Remove(sb.Length - 3, 3);
                        }

                        sb.AppendLine("        ]);");
                        sb.AppendLine("");


                        switch (grafikTipi)
                        {
                            case 0:
                                sb.AppendLine("        var options = {");
                                sb.AppendLine(string.Format("          title: '{0}'", raporAdi));
                                sb.AppendLine("        };");
                                sb.AppendLine("");
                                sb.AppendLine("        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));");
                                break;
                            case 1:
                                sb.AppendLine("        var options = {");
                                sb.AppendLine(string.Format("          title: '{0}',", raporAdi));
                                sb.AppendLine("          hAxis: {title: '" + col1name + "', titleTextStyle: {color: 'red'}}");
                                sb.AppendLine("        };");
                                sb.AppendLine("");
                                sb.AppendLine("        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));");
                                break;
                            case 2:
                                sb.AppendLine("        var options = {");
                                sb.AppendLine(string.Format("          title: '{0}',", raporAdi));
                                sb.AppendLine("          hAxis: {title: '" + col1name + "', titleTextStyle: {color: 'red'}}");
                                sb.AppendLine("        };");
                                sb.AppendLine("");
                                sb.AppendLine("        var chart = new google.visualization.BarChart(document.getElementById('chart_div'));");
                                break;
                        }

                        sb.AppendLine("        chart.draw(data, options);");
                        sb.AppendLine("      }");
                        sb.AppendLine("    </script>");
                        sb.AppendLine("  </head>");
                        sb.AppendLine("  <body>");
                        sb.AppendLine("    <div id=\"chart_div\" style=\"width: 400px; height: 240px;\"></div>");
                        sb.AppendLine("  </body>");
                        sb.AppendLine("</html>");

                        FileStream fs;
                        if (!Directory.Exists(Path.GetTempPath() + "\\" + Config.Get.FirmaKod))
                            Directory.CreateDirectory(Path.GetTempPath() + "\\" + Config.Get.FirmaKod);

                        FilePath = Path.GetTempPath() + Config.Get.FirmaKod + "\\" + raporAdi + "-" + Config.Get.FirmaKod + ".html";
                        if (File.Exists(FilePath))
                            fs = new FileStream(FilePath, FileMode.Truncate, FileAccess.ReadWrite);
                        else
                            fs = new FileStream(FilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                        //Encoding m_Encoding;
                        //m_Encoding = Encoding.GetEncoding("iso-8859-9");
                        StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                        sw.Write(sb.ToString());
                        sw.Close();
                        fs.Close();
                        FTPuploadFile("ftp.mikroyardim.com", "mikroyardim.com-dev", "1Q2w3e4r", FilePath);
                    }
                }
                sqlConn.Close();
            }
        }

        public void FTPuploadFile(string ftpadresi, string kullaniciadi, string sifre, string DosyaYolu)
        {
            string path = @"ftp://" + ftpadresi + @"/" + Config.Get.FirmaKod + @"/" + Path.GetFileName(DosyaYolu);
            FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(path);
            request.Method = WebRequestMethods.Ftp.UploadFile;
            request.Credentials = new NetworkCredential(kullaniciadi, sifre);
            request.UsePassive = true;
            request.UseBinary = true;
            request.KeepAlive = false;
            FileStream stream = File.OpenRead(DosyaYolu);
            byte[] buffer = new byte[stream.Length];
            stream.Read(buffer, 0, buffer.Length);
            stream.Close();
            Stream reqStream = request.GetRequestStream();
            reqStream.Write(buffer, 0, buffer.Length);
            reqStream.Close();
        }

        private void KlasorTemizle()
        {
            string path = Path.GetTempPath() + @"\" + Config.Get.FirmaKod;
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            DirectoryInfo dir = new DirectoryInfo(path);
            IEnumerable<FileInfo> fileList = dir.GetFiles("*.html", SearchOption.AllDirectories);
            foreach (var item in fileList)
            {
                File.Delete(item.FullName);
            }

        }

        public void RaporLisetele()
        {
            StringBuilder sb = new StringBuilder();
            //DirectoryInfo dir = new DirectoryInfo(Path.GetTempPath() + @"\" + Config.Get.FirmaKod);
            //IEnumerable<FileInfo> fileList = dir.GetFiles("*.html", SearchOption.AllDirectories);
            sb.AppendLine("<html>");
            sb.AppendLine("<head>");
            sb.AppendLine("<title>");
            sb.AppendLine("Raporlar Listesi");
            sb.AppendLine("</title>");
            sb.AppendLine("<meta charset=iso-8859-9 />");
            sb.AppendLine("<meta name='author' content='yasin ozbey' />");
            sb.AppendLine("<meta name='copyright' content='copyright 2008 www.mikroyadim.com' />");
            sb.AppendLine("<meta name='viewport' content='width=device-width; initial-scale=1.0; maximum-scale=1.0;' />");
            sb.AppendLine("<meta http-equiv='Page-Enter' content='progid:DXImageTransform.Microsoft.Slide(duration=2,irisStyle=star)'> ");
            sb.AppendLine("<meta http-equiv='Page-Exit' content='progid:DXImageTransform.Microsoft.Slide(duration=2,irisStyle=star)'>");
            sb.AppendLine("<style>");
            sb.AppendLine("@import url('../iphone.css');");
            sb.AppendLine("</style>");
            sb.AppendLine("<script type='text/javascript' src='../orientation.js'></script>");
            sb.AppendLine("<script type='text/javascript'>");
            sb.AppendLine("window.addEventListener('load', function() { setTimeout(loaded, 100) }, false);");
            sb.AppendLine("function loaded() {");
            sb.AppendLine("document.getElementById('page_wrapper').style.visibility = 'visible';");
            sb.AppendLine("window.scrollTo(0, 1); }");
            sb.AppendLine("</script>");
            sb.AppendLine("</head>");
            sb.AppendLine("<body onorientationchange='updateOrientation()'>");
            sb.AppendLine("<table style='width: 100%; height: 55px' >");
            foreach (var item in Config.Get.Jobs)
            {
                sb.AppendLine("<tr>");
                sb.AppendLine("<td background='../iphone1.jpg' style='height: 43; width: 100%'><center><a href='" + item.RaporAdi + "-" + Config.Get.FirmaKod + ".html'>" + item.RaporAdi + "</a></center></td>");
                sb.AppendLine("</tr>");
            }
            sb.AppendLine("</body>");
            sb.AppendLine("</html>");
            FilePath = Path.GetTempPath() + @"\" + Config.Get.FirmaKod + @"\index.html";
            FileStream fs;
            if (File.Exists(FilePath))
                fs = new FileStream(FilePath, FileMode.Truncate, FileAccess.ReadWrite);
            else
                fs = new FileStream(FilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            //Encoding m_Encoding;
            //m_Encoding = Encoding.GetEncoding("iso-8859-9");
            StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
            sw.Write(sb.ToString());
            sw.Close();
            fs.Close();

            FTPuploadFile("ftp.mikroyardim.com", "mikroyardim.com-dev", "1Q2w3e4r", FilePath);
        }

        public void CreateDirectoryOnFTP(String inFTPServerAndPath, String inUsername, String inPassword, String inNewDirectory)
        {
            try
            {
                FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create("ftp://" + inFTPServerAndPath + @"/" + inNewDirectory);
                request.Credentials = new NetworkCredential(inUsername, inPassword);
                request.UsePassive = true;
                request.UseBinary = true;
                request.KeepAlive = false;
                request.Method = WebRequestMethods.Ftp.MakeDirectory;
                FtpWebResponse makeDirectoryResponse = (FtpWebResponse)request.GetResponse();
            }
            catch (Exception)
            {
            }
        }

        public bool InternetTest()
        {
            string url = @"http://www.google.com";
            try
            {
                System.Net.WebRequest myRequest = System.Net.WebRequest.Create(url);
                System.Net.WebResponse myResponse = myRequest.GetResponse();
            }
            catch (System.Net.WebException)
            {
                return false;
            }
            return true;
        }

        public void RaporOku()
        {
            Config.Get.Jobs.Clear();
            using (SqlConnection sqlConn = new SqlConnection("server=mssql03.natro.com;user=USR121031105745;pwd=PSS!B6T1Z5%;database=DB121031105745;"))
            {
                sqlConn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT     Raporlar.ID, Raporlar.RaporIsim, Raporlar.RaporSQL, Raporlar.RaporZaman,Raporlar.RaporTipi,Raporlar.GrafikTipi FROM         Kullanicilar INNER JOIN Raporlar ON Kullanicilar.ID = Raporlar.KullaniciID WHERE     (Kullanicilar.Adi = @adi)", sqlConn))
                {
                    cmd.Parameters.Add("@adi", SqlDbType.NVarChar, 50).Value = Config.Get.FirmaKod;
                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        Job jb = new Job();
                        jb.KalanSure = rd.GetInt32(3);
                        jb.RaporAdi = rd.GetString(1);
                        jb.Sql = rd.GetString(2);
                        jb.Sure = rd.GetInt32(3);
                        jb.RaporTipi = rd.GetByte(4);
                        jb.GrafikTipi = rd.GetByte(5);

                        Config.Get.Jobs.Add(jb);
                    }
                    rd.Close();
                }
                sqlConn.Close();
            }
        }
    }
}
